import datetime
import queue
import random
import re
import time

import pymysql as pymysql
from fake_useragent import UserAgent
from lxml import etree
from selenium import webdriver
from selenium.webdriver.chrome.options import Options


class footballOver:
    chrome_path = r'./chromedriver.exe'

    def __init__(self, url):
        chrome_options = Options()
        chrome_options.add_argument('--headless')
        chrome_options.add_argument('--disable-gpu')
        self.q = queue.Queue()
        self.driver = webdriver.Chrome(chrome_options=chrome_options)
        self.base_url = url
        self.__host = '81.69.226.125'
        self.__port = 4510
        self.__user = 'root'
        self.__password = 'root'
        self.__db = 'titan007'
        self.__charset = 'utf8mb4'
        self.lists = []
        self.dicts = {}
        self.s = ""
        self.ua = UserAgent()
        self.__conn = pymysql.connect(
            host=self.__host,
            port=self.__port,
            user=self.__user,
            password=self.__password,
            db=self.__db,
            charset=self.__charset
        )

    def sql_operate(self, lists):
        cursor = self.__conn.cursor()
        for single in lists:
            # for key, value in single.items():
            #     print(key, value)
            # if not value:
            #     value = 'null'
            #     single[key] = value
            sql_inert = "insert into tp_game_his values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
            data = [(single['id'], single['game_type'], single['odds_type'], single['play_type'], single['match_name'],
                     single['match_date'], single['home_team'],
                     single['away_team'], single['half_score'], single['final_score'], single['match_status'],
                     single['fx_id'], single['hc'], single['hc_home'], single['hc_away'], single['style'],
                     single['sort']
                     , single['odds_change_time'], single['create_time'], single['update_time'])]
            print(data)
            print('正在插入中')
            cursor.executemany(sql_inert, data)
            self.__conn.commit()
        cursor.close()
        self.__conn.close()

    def get_html(self):
        self.driver.get(self.base_url)
        complete_data = self.driver.page_source
        return complete_data

    # 解析出详情页url
    def parse_detail_url(self, complete_data):
        complete_html = etree.HTML(complete_data)
        total_tr = complete_html.xpath('//tbody/tr[@align="center"]')
        return total_tr

    # 组装数据
    def assemble_data(self, total_tr):
        for tr in total_tr[1:]:
            match_name = "".join(tr.xpath('td[1]/font/text()'))
            match_date = "".join(tr.xpath('td[2]/text()'))
            match_status = "".join(tr.xpath('td[3]/text()'))
            home_team = "".join(tr.xpath('td[4]/text()'))
            final_score = str(tr.xpath('td[5]/font/text()')).replace(",", "-").replace("'", '').replace(' ', '').replace('[','').replace(']','')
            away_team = "".join(tr.xpath('td[6]/text()'))
            half_score = str(tr.xpath('td[7]/font/text()')).replace(",", "-").replace("'", '').replace(' ', '').replace('[','').replace(']','')
            minutes = "".join(tr.xpath('td[3]/text()'))
            ou = "".join(tr.xpath('td[9]/text()'))

            id = random.randint(1, 9999999)
            times = str(time.time())[:10]
            # print(match_name, match_date, match_status, home_team, final_score, away_team, half_score)
            self.dicts = {
                "id": id,
                "game_type": 1,
                "odds_type": "1",
                "play_type": "1",
                "match_name": match_name,
                "match_date": match_date,
                "home_team": home_team,
                "away_team": away_team,
                "half_score": half_score,
                "final_score": final_score,
                "match_status": match_status,
                "fx_id": "",
                "hc": "",
                "hc_home": "",
                "hc_away": "",
                "style": "",
                "sort": "",
                "odds_change_time": "",
                "create_time": times,
                "update_time": times
            }
            self.lists.append(self.dicts)
        self.driver.close()
        self.sql_operate(self.lists)
        return self.lists

    def main(self):
        complete_data = self.get_html()
        total_tr = self.parse_detail_url(complete_data)
        self.assemble_data(total_tr)


if __name__ == '__main__':
    current_date = str(datetime.date.today())
    current_date = re.sub('-', "", current_date)
    current_date = ["https://bf.titan007.com/football/Over_{0}.htm".format(int(current_date) - i) for i in range(1, 4)]
    print(current_date)
    for url in current_date:
        run = footballOver(url)
        run.main()
